﻿using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Adams.Public.DataAccess
{
    public static class Businesses
    {
        public static Adams.Public.DataTransfer.Businesses.TypeCollection GetTypesAndCategories()
        {
            var data = new Adams.Public.DataTransfer.Businesses.TypeCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * FROM Businesses.Types ORDER BY Name
SELECT C.*, (SELECT COUNT(*) FROM Businesses.Businesses P WHERE P.CategoryID = C.CategoryID AND P.StatusID = 2 AND DATEDIFF(d, GETDATE(), P.ExpiresOn) >= 0) AS Businesss FROM Businesses.Categories C ORDER BY C.Name
", CommandType.Text))
            {
                while (dr.Read())
                {
                    var type = new Adams.Public.DataTransfer.Businesses.Type();
                    type.Categories2 = new Adams.Public.DataTransfer.Businesses.CategoryCollection();
                    type.ID = dr["TypeID"].DbInt();
                    type.Name = dr["Name"].DbString();
                    data.Add(type);
                }
                dr.NextResult();
                while (dr.Read())
                {
                    var cat = new Adams.Public.DataTransfer.Businesses.Category();
                    cat.ID = dr["CategoryID"].DbInt();
                    cat.Name = dr["Name"].DbString();
                    cat.Businesss = dr["Businesss"].DbInt();
                    data.Where(t => t.ID == dr["TypeID"].DbInt()).FirstOrDefault().Categories2.Add(cat);
                }
            }

            return data;
        }

        public static Adams.Shared.Entities.Businesses.BusinessCollection ListBusinesss(Adams.Public.DataTransfer.Businesses.ListBusinesssInput input)
        {
            var data = new Adams.Shared.Entities.Businesses.BusinessCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT P.BusinessID,P.CategoryID,P.StatusID,P.Name,P.Street,P.Street2,P.City,P.State,P.Zip,P.Phone,P.Description,P.CreatedOn,P.ExpiresOn,C.Name As Category,T.TypeID AS TypeID, T.Name AS Type
FROM Businesses.Businesses P 
INNER JOIN Businesses.Categories C ON P.CategoryID = C.CategoryID 
INNER JOIN Businesses.Types T ON C.TypeID = T.TypeID 
WHERE T.Name = @Type" + (string.IsNullOrEmpty(input.Category) ? "" : @" AND C.Name = @Category") + @"
AND P.StatusID = 2 AND DATEDIFF(d, GETDATE(), P.ExpiresOn) >= 0
ORDER BY ExpiresOn DESC
", CommandType.Text,
    DB.CreateParameter("@Type", input.Type.ToNString()),
    DB.CreateParameter("@Category", input.Category.ToNString())))
            {
                while (dr.Read())
                {
                    var Business = new Shared.Entities.Businesses.Business();
                    Business.ID = dr["BusinessID"].DbInt();
                    Business.Status = (Shared.Entities.Businesses.Status)dr["StatusID"].DbInt();
                    Business.Name = dr["Name"].DbString();
                    Business.Street = dr["Street"].DbString();
                    Business.Street2 = dr["Street2"].DbString();
                    Business.City = dr["City"].DbString();
                    Business.State = dr["State"].DbString();
                    Business.Zip = dr["Zip"].DbString();
                    Business.Phone = dr["Phone"].DbString();
                    Business.Description = dr["Description"].DbString();
                    Business.CreatedOn = dr["CreatedOn"].DbDate();
                    Business.ExpiresOn = dr["ExpiresOn"].DbDate();
                    data.Add(Business);
                }
            }

            return data;
        }

        public static Adams.Shared.Entities.Businesses.BusinessCollection SearchBusinesss(Adams.Public.DataTransfer.Businesses.SearchBusinesssInput input)
        {
            var data = new Adams.Shared.Entities.Businesses.BusinessCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT P.BusinessID,P.CategoryID,P.StatusID,P.Name,P.Street,P.Street2,P.City,P.State,P.Zip,P.Phone,P.Description,P.CreatedOn,P.ExpiresOn,C.Name As Category,T.TypeID AS TypeID, T.Name AS Type
FROM Businesses.Businesses P 
INNER JOIN Businesses.Categories C ON P.CategoryID = C.CategoryID 
INNER JOIN Businesses.Types T ON C.TypeID = T.TypeID 
WHERE FREETEXT ((P.Name,P.Street,P.Street2,P.City,P.State,P.Zip,P.Phone,P.Description), '" + input.Search.Replace("'", "''") + @"' )
AND P.StatusID =2  AND DATEDIFF(d, GETDATE(), P.ExpiresOn) >= 0
ORDER BY ExpiresOn DESC
", CommandType.Text,
    DB.CreateParameter("@Search", input.Search.ToNString())))
            {
                while (dr.Read())
                {
                    var Business = new Shared.Entities.Businesses.Business();
                    Business.ID = dr["BusinessID"].DbInt();
                    Business.Status = (Shared.Entities.Businesses.Status)dr["StatusID"].DbInt();
                    Business.Name = dr["Name"].DbString();
                    Business.Street = dr["Street"].DbString();
                    Business.Street2 = dr["Street2"].DbString();
                    Business.City = dr["City"].DbString();
                    Business.State = dr["State"].DbString();
                    Business.Zip = dr["Zip"].DbString();
                    Business.Phone = dr["Phone"].DbString();
                    Business.Description = dr["Description"].DbString();
                    Business.CreatedOn = dr["CreatedOn"].DbDate();
                    Business.ExpiresOn = dr["ExpiresOn"].DbDate();

                    Business.Category = new Shared.Entities.Businesses.Category()
                    {
                        ID = dr["CategoryID"].DbInt(),
                        Name = dr["Type"].DbString(),
                        Type = new Shared.Entities.Businesses.Type()
                        {
                            ID = dr["TypeID"].DbInt(),
                            Name = dr["Type"].DbString()
                        }
                    };

                    data.Add(Business);
                }
            }

            return data;
        }

        public static Adams.Shared.Entities.Businesses.Business Business(Adams.Public.DataTransfer.Businesses.BusinessInput input)
        {
            var data = new Adams.Shared.Entities.Businesses.Business();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT P.BusinessID,P.CategoryID,P.StatusID,P.Name,P.Street,P.Street2,P.City,P.State,P.Zip,P.Phone,P.Description,P.CreatedOn,P.ExpiresOn,C.Name As Category,T.TypeID AS TypeID, T.Name AS Type
FROM Businesses.Businesses P 
INNER JOIN Businesses.Categories C ON P.CategoryID = C.CategoryID 
INNER JOIN Businesses.Types T ON C.TypeID = T.TypeID 
WHERE BusinessID = @BusinessID
", CommandType.Text,
    DB.CreateParameter("@BusinessID", input.BusinessID)))
            {
                dr.Read();
                data.ID = dr["BusinessID"].DbInt();
                data.Status = (Shared.Entities.Businesses.Status)dr["StatusID"].DbInt();
                data.Name = dr["Name"].DbString();
                data.Street = dr["Street"].DbString();
                data.Street2 = dr["Street2"].DbString();
                data.City = dr["City"].DbString();
                data.State = dr["State"].DbString();
                data.Zip = dr["Zip"].DbString();
                data.Phone = dr["Phone"].DbString();
                data.Description = dr["Description"].DbString();
                data.CreatedOn = dr["CreatedOn"].DbDate();
                data.ExpiresOn = dr["ExpiresOn"].DbDate();

                data.Category = new Shared.Entities.Businesses.Category()
                {
                    ID = dr["CategoryID"].DbInt(),
                    Name = dr["Type"].DbString(),
                    Type = new Shared.Entities.Businesses.Type()
                    {
                        ID = dr["TypeID"].DbInt(),
                        Name = dr["Type"].DbString()
                    }
                };
            }

            return data;
        }

        public static Adams.Public.DataTransfer.Businesses.GetMemberBusinesss GetMemberBusinesss(Adams.Public.DataTransfer.Businesses.GetMemberBusinesssInput input)
        {
            var data = new Adams.Public.DataTransfer.Businesses.GetMemberBusinesss();
            data.Businesss = new Shared.Entities.Businesses.BusinessCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT P.BusinessID,P.CategoryID,P.MemberID,P.StatusID,P.Name,P.Street,P.Street2,P.City,P.State,P.Zip,P.Phone,P.Description,P.CreatedOn,P.ExpiresOn,M.FirstName,M.LastName,M.Email,C.Name As Category,T.TypeID AS TypeID, T.Name AS Type
FROM Businesses.Businesses P 
INNER JOIN Businesses.Categories C ON P.CategoryID = C.CategoryID 
INNER JOIN Businesses.Types T ON C.TypeID = T.TypeID 
INNER JOIN Membership.Members M ON P.MemberID = M.MemberID
WHERE P.MemberID = @MemberID 
AND P.StatusID <> 4 AND DATEDIFF(d, GETDATE(), P.ExpiresOn) >= -7
ORDER BY ExpiresOn ASC
", CommandType.Text,
    DB.CreateParameter("@MemberID", input.MemberID)))
            {
                while (dr.Read())
                {
                    var Business = new Shared.Entities.Businesses.Business();
                    Business.ID = dr["BusinessID"].DbInt();
                    Business.Status = (Shared.Entities.Businesses.Status)dr["StatusID"].DbInt();
                    Business.Name = dr["Name"].DbString();
                    Business.Street = dr["Street"].DbString();
                    Business.Street2 = dr["Street2"].DbString();
                    Business.City = dr["City"].DbString();
                    Business.State = dr["State"].DbString();
                    Business.Zip = dr["Zip"].DbString();
                    Business.Phone = dr["Phone"].DbString();
                    Business.Description = dr["Description"].DbString();
                    Business.CreatedOn = dr["CreatedOn"].DbDate();
                    Business.ExpiresOn = dr["ExpiresOn"].DbDate();

                    Business.Category = new Shared.Entities.Businesses.Category()
                    {
                        ID = dr["CategoryID"].DbInt(),
                        Name = dr["Type"].DbString(),
                        Type = new Shared.Entities.Businesses.Type()
                        {
                            ID = dr["TypeID"].DbInt(),
                            Name = dr["Type"].DbString()
                        }
                    };

                    Business.Member = new Shared.Entities.Membership.Member()
                    {
                        ID = dr["MemberID"].DbInt(),
                        Email = dr["Email"].DbString(),
                        FirstName = dr["FirstName"].DbString(),
                        LastName = dr["LastName"].DbString(),
                    };

                    data.Businesss.Add(Business);
                }
            }

            return data;            
        }

        public static void Renew(Adams.Public.DataTransfer.Businesses.RenewSave input)
        {
            DB.Run(@"
UPDATE Businesses.Businesses
SET ExpiresOn = @ExpiresOn
WHERE BusinessID = @BusinessID AND MemberID = @MemberID
", CommandType.Text,
                    DB.CreateParameter("@BusinessID", input.BusinessID, SqlDbType.Int),
                    DB.CreateParameter("@MemberID", input.MemberID, SqlDbType.Int),
                    DB.CreateParameter("@ExpiresOn", input.ExpiresOn));
        }

        public static void Remove(Adams.Public.DataTransfer.Businesses.RemoveInput input)
        {
            DB.Run(@"
UPDATE Businesses.Businesses
SET StatusID = 4
WHERE BusinessID = @BusinessID AND MemberID = @MemberID
", CommandType.Text,
                    DB.CreateParameter("@BusinessID", input.BusinessID, SqlDbType.Int),
                    DB.CreateParameter("@MemberID", input.MemberID, SqlDbType.Int));
        }
    }
}
